﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using MN.Model;
using DLL;
namespace MN.DAL
{
    public class Notice
    {
        //新增对象 事务 
        public void Insert(NoticeModel notice, SqlTransaction tran)
        {
            string strSql = "INSERT INTO [Notice]( [userId],[title],[content],[createTime],[dataExist]) VALUES(@userId,@title,@content,@createTime,@dataExist)";
            //获取命令参数数组 
            SqlParameter[] parms = new SqlParameter[] { 
                                    new SqlParameter("@userId", SqlDbType.VarChar), 
                                    new SqlParameter("@title", SqlDbType.VarChar), 
                                    new SqlParameter("@content", SqlDbType.VarChar), 
                                    new SqlParameter("@createTime", SqlDbType.DateTime),
                                    new SqlParameter("@dataExist", SqlDbType.Bit),
};
            int index = 0;
            parms[index++].Value = notice.UserId;
            parms[index++].Value = notice.Title;
            parms[index++].Value = notice.Content;
            parms[index++].Value = notice.CreateTime;
            parms[index++].Value = notice.DataExist;
            if (tran == null)
                notice.Pkid = SqlHelper.ExecuteNonQuery(CommandType.Text, strSql, parms);
            else
                notice.Pkid = SqlHelper.ExecuteNonQuery(CommandType.Text, strSql, parms);
        }
        //新增对象 无事务 
        public void Insert(NoticeModel notice) { Insert(notice, null); }
        //更新对象 事务 
        public void Update(NoticeModel notice, SqlTransaction tran)
        {
            string strSql = "UPDATE [Notice] SET  [userId] = @userId, [title] = @title, [content] = @content, [createTime] = @createTime, [dataExist] = @dataExist WHERE [pkid] = @pkid";
            //获取命令参数数组 
            SqlParameter[] parms = new SqlParameter[] { 
new SqlParameter("@userId", SqlDbType.VarChar),
new SqlParameter("@title", SqlDbType.VarChar),
new SqlParameter("@content", SqlDbType.VarChar),
new SqlParameter("@createTime", SqlDbType.DateTime),
new SqlParameter("@dataExist", SqlDbType.Bit),
new SqlParameter("@pkid", SqlDbType.Int),
};
            int index = 0;
            parms[index++].Value = notice.UserId;
            parms[index++].Value = notice.Title;
            parms[index++].Value = notice.Content;
            parms[index++].Value = notice.CreateTime;
            parms[index++].Value = notice.DataExist;
            parms[index++].Value = notice.Pkid;
            if (tran == null)
                SqlHelper.ExecuteNonQuery(CommandType.Text, strSql, parms);
            else
                SqlHelper.ExecuteNonQuery(CommandType.Text, strSql, parms);
        }
        //更新对象 无事务 
        public void Update(NoticeModel notice) { Update(notice, null); }
        public void Delete(int pkid, SqlTransaction tran)
        {
            string strSql = "DELETE FROM [Notice] WHERE pkid = @pkid";
            //获取命令参数数组 
            SqlParameter parm = new SqlParameter("@pkid", SqlDbType.Int);
            parm.Value = pkid;
            if (tran == null)
                SqlHelper.ExecuteNonQuery(CommandType.Text, strSql, parm);
            else
                SqlHelper.ExecuteNonQuery(CommandType.Text, strSql, parm);
        }
        public void Delete(int pkid) { Delete(pkid, null); }
        public NoticeModel GetModel(int pkid)
        {
            string strSql = "SELECT * FROM [Notice](NOLOCK) WHERE pkid = @pkid";
            NoticeModel notice = null;
            //获取命令参数数组 
            SqlParameter parm = new SqlParameter("@pkid", SqlDbType.Int);
            parm.Value = pkid;
            using (SqlDataReader sdr = SqlHelper.ExecuteReader(CommandType.Text, strSql, parm))
            {
                if (sdr.Read())
                {
                    notice = new NoticeModel();
                    notice.Pkid = sdr["pkid"] == DBNull.Value ? 0 : Convert.ToInt32(sdr["pkid"]);
                    notice.UserId = sdr["userId"].ToString();
                    notice.Title = (sdr["title"].ToString());
                    notice.Content = (sdr["content"].ToString());
                    notice.CreateTime = (sdr["createTime"].ToString());
                    notice.DataExist = sdr["dataExist"] == DBNull.Value ? false : Convert.ToBoolean(sdr["dataExist"]);
                }
            } return notice;
        }

        public List<NoticeModel> GetList(int pageIndex, int pageSize, string strWhere)
        {
            List<NoticeModel> noticeList = new List<NoticeModel>();
            StringBuilder strSql = new StringBuilder();
            if (pageIndex > 0 && pageSize > 0)
            {
                string strTop = "TOP " + (pageSize * pageIndex).ToString();
                strSql.Append("SELECT ").Append(strTop).Append(" * FROM [Notice](NOLOCK)").Append(strWhere);
            }
            else
                strSql.Append("SELECT * FROM [Notice](NOLOCK)").Append(strWhere);
            using (SqlDataReader sdr = SqlHelper.ExecuteReader(CommandType.Text, strSql.ToString(), null))
            {
                if (pageIndex > 0 && pageSize > 0)
                {
                    int cnt = 1;
                    int pageLowerBound = (pageIndex - 1) * pageSize + 1;
                    int pageUpperBound = pageLowerBound + pageSize - 1;
                    while (sdr.Read())
                    {
                        if (cnt >= pageLowerBound && cnt <= pageUpperBound)
                        {
                            NoticeModel notice = new NoticeModel();
                            notice.Pkid = sdr["pkid"] == DBNull.Value ? 0 : Convert.ToInt32(sdr["pkid"]);
                            notice.UserId = sdr["userId"].ToString();
                            notice.Title = sdr["title"].ToString();
                            notice.Content = sdr["content"].ToString();
                            notice.CreateTime = sdr["createTime"].ToString();
                            notice.DataExist = sdr["dataExist"] == DBNull.Value ? false : Convert.ToBoolean(sdr["dataExist"]);
                            noticeList.Add(notice);
                        } cnt++;
                    }
                }
                else
                {
                    while (sdr.Read())
                    {
                        NoticeModel notice = new NoticeModel();
                        notice.Pkid = sdr["pkid"] == DBNull.Value ? 0 : Convert.ToInt32(sdr["pkid"]);
                        notice.UserId = sdr["userId"].ToString();
                        notice.Title = sdr["title"].ToString();
                        notice.Content = sdr["content"].ToString();
                        notice.CreateTime = sdr["createTime"].ToString();
                        notice.DataExist = sdr["dataExist"] == DBNull.Value ? false : Convert.ToBoolean(sdr["dataExist"]);
                        noticeList.Add(notice);
                    }
                }
            } return noticeList;
        }
        public int DoCount(string strWhere)
        {
            string strSql = "SELECT COUNT(pkid) AS TotalCount FROM [Notice](NOLOCK)" + strWhere;
            return Convert.ToInt32(SqlHelper.ExecuteScalar(CommandType.Text, strSql, null));
        }
    }
}

